// HotAnalysisMapper.java
package com.scuec.groupfive.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
public interface HotAnalysisMapper {

    // 获取最活跃用户
    @Select("SELECT u.user_id AS userId, u.username AS name, " +
            "COUNT(DISTINCT p.post_id) AS postCount, " +
            "COUNT(DISTINCT pc.comment_id) AS commentCount, " +
            "COUNT(DISTINCT pl.like_id) AS likeCount, " +
            "COUNT(DISTINCT pf.favorite_id) AS favoriteCount " +
            "FROM users u " +
            "LEFT JOIN posts p ON u.user_id = p.user_id " +
            "LEFT JOIN post_comments pc ON u.user_id = pc.user_id " +
            "LEFT JOIN post_likes pl ON u.user_id = pl.user_id " +
            "LEFT JOIN post_favorites pf ON u.user_id = pf.user_id " +
            "WHERE u.role = 'student' " + // 只查询学生用户
            "AND (#{startDate} IS NULL OR p.created_at >= #{startDate}) " +
            "AND (#{endDate} IS NULL OR p.created_at <= #{endDate}) " +
            "GROUP BY u.user_id, u.username " +
            "ORDER BY (postCount + commentCount + likeCount + favoriteCount) DESC")
    List<Map<String, Object>> getMostActiveUsers(@Param("startDate") Date startDate,
                                                 @Param("endDate") Date endDate);

    // 修改计数查询，添加管理员过滤
    @Select("SELECT COUNT(DISTINCT u.user_id) as total " +
            "FROM users u " +
            "LEFT JOIN posts p ON u.user_id = p.user_id " +
            "LEFT JOIN post_comments pc ON u.user_id = pc.user_id " +
            "LEFT JOIN post_likes pl ON u.user_id = pl.user_id " +
            "LEFT JOIN post_favorites pf ON u.user_id = pf.user_id " +
            "WHERE u.role = 'student' " + // 只查询学生用户
            "AND (#{startDate} IS NULL OR p.created_at >= #{startDate}) " +
            "AND (#{endDate} IS NULL OR p.created_at <= #{endDate})")
    int countTotalActiveUsers(@Param("startDate") Date startDate,
                              @Param("endDate") Date endDate);

    // 获取互动高峰时段
    @Select("SELECT CONCAT(LPAD(HOUR(created_at), 2, '0'), ':00-', " +
            "LPAD(HOUR(created_at)+1, 2, '0'), ':00') AS timeRange, " +
            "COUNT(*) AS interactionCount " +
            "FROM (" +
            "   SELECT created_at FROM posts " +
            "   UNION ALL SELECT created_at FROM post_comments " +
            "   UNION ALL SELECT created_at FROM post_likes " +
            "   UNION ALL SELECT created_at FROM post_favorites " +
            ") AS all_interactions " +
            "WHERE (#{startDate} IS NULL OR created_at >= #{startDate}) " +
            "AND (#{endDate} IS NULL OR created_at <= #{endDate}) " +
            "GROUP BY timeRange " +
            "ORDER BY interactionCount DESC " +
            "LIMIT 2")
    List<Map<String, Object>> getPeakHours(@Param("startDate") Date startDate,
                                           @Param("endDate") Date endDate);

    // 获取每周互动模式
    @Select("SELECT DAYOFWEEK(created_at) - 1 AS dayOfWeek, " +
            "COUNT(*) AS interactionCount " +
            "FROM (" +
            "   SELECT created_at FROM posts " +
            "   UNION ALL SELECT created_at FROM post_comments " +
            "   UNION ALL SELECT created_at FROM post_likes " +
            "   UNION ALL SELECT created_at FROM post_favorites " +
            ") AS all_interactions " +
            "WHERE (#{startDate} IS NULL OR created_at >= #{startDate}) " +
            "AND (#{endDate} IS NULL OR created_at <= #{endDate}) " +
            "GROUP BY dayOfWeek " +
            "ORDER BY dayOfWeek")
    List<Map<String, Object>> getWeeklyPattern(@Param("startDate") Date startDate,
                                               @Param("endDate") Date endDate);

    // 获取热门标签
    @Select("SELECT type AS tag, COUNT(*) AS count " +
            "FROM posts " +
            "WHERE (#{startDate} IS NULL OR created_at >= #{startDate}) " +
            "AND (#{endDate} IS NULL OR created_at <= #{endDate}) " +
            "AND status = 1 " +  // 只统计已发布的帖子
            "GROUP BY type " +
            "ORDER BY count DESC " +
            "LIMIT 5")
    List<Map<String, Object>> getPopularTags(@Param("startDate") Date startDate,
                                             @Param("endDate") Date endDate);
}